/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Order;
import dto.OrderItems;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author duonghung1369
 */
public class OrderDAO extends BaseDAO<Order> {

    public OrderDAO(Class<Order> clazz) {
        super(clazz);
    }

    public void saveOrder(Order order) {
        try {
            session.getTransaction().begin();
            String sqlOrder = "insert into [Order] values (?,?,?,?,?,?,?,?,?)";
            Query query = session.createQuery(sqlOrder);
            query.setString("confirmation_string", order.getConfirmationString());
            query.setDate("delivered_datetime", order.getDeliveredDatetime());
            query.setBoolean("is_deleted", Boolean.FALSE);
            query.setString("note", order.getNote());
            query.setInteger("order_status_id", order.getOrderStatus().getId());
            query.setDate("placed_datetime", order.getPlacedDatetime());
            query.setDouble("shipping_charges", order.getShippingCharges());
            query.setDate("update_date", order.getUpdateDate());
            query.setInteger("customer_id", order.getUser().getId());

            // insert order
            query.executeUpdate();

            String sqlOrderItem = "insert into OrderItems values(?,?,?,?,?)";
            query = session.createQuery(sqlOrderItem);
            for (OrderItems item : order.getOrderItemses()) {
                query = session.createQuery(sqlOrderItem);
                query.setDate("delivered_datetime", item.getDeliveredDatetime());
                query.setBoolean("is_deleted", item.isIsDeleted());
//                query.setInteger("order_id", );
                query.setDate("product_id", item.getDeliveredDatetime());
                query.setDate("quantity", item.getDeliveredDatetime());
            }

        } catch (HibernateException ex) {
            if (session.getTransaction().isActive()) {
                session.getTransaction().rollback();
            }
        }
    }

    /**
     * Search order by criteria, if criteria is null, search all.
     * @param confirmation
     * @param sStartDate
     * @param sEndDate
     * @param orderStatus
     * @return
     * @throws ParseException
     */
    public List<Order> searchOrder(String confirmation, String sStartDate, String sEndDate, String orderStatus) throws ParseException {
        session.beginTransaction();
        Criteria criteria = session.createCriteria(type);
        if (confirmation != null) {
            criteria = criteria.add(Restrictions.eq("confirmationString", confirmation.trim()));
        }

        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

        Date startDate = null;
        Date endDate = null;
        if (sStartDate != null) {
            startDate = new Date(df.parse(sStartDate.trim()).getTime());
            criteria = criteria.add(Restrictions.ge("placedDatetime", startDate));
        }
        if (sEndDate != null) {
            endDate = new Date(df.parse(sEndDate.trim()).getTime());
            criteria = criteria.add(Restrictions.le("placedDatetime", endDate));
        }

        if (orderStatus != null) {
            int orderStatusId = Integer.parseInt(orderStatus);
            criteria = criteria.add(Restrictions.eq("orderStatus.id", orderStatusId));
        }

        // check if startDate > endDate
        if (startDate != null && endDate != null) {
            if (startDate.getTime() > endDate.getTime()) {
                return null;
            }
        }
        return criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY).list();
    }

    /**
     * Get all order without duplicate result.
     * @return
     */
    public List<Order> getAll() {
        session.beginTransaction();
        String sql = "from Order order by placed_datetime desc";
        Query query = session.createQuery(sql);
        return query.list();
    }

    /**
     * Get all orders that user bought.
     * @param userId
     * @return
     */
    public List<Order> getOrdersHistory(int userId) {
        session.beginTransaction();
        String sql = "from Order where user.id = :id order by placed_datetime desc";
        Query query = session.createQuery(sql);
        query.setInteger("id", userId);
        return query.list();
    }

    /**
     * Calculate total price for an order
     * @param id orderId
     * @return total price of order items.
     */
    public double calculateOrderTotal(int id) {
        session.beginTransaction();
        Object obj = session.get(Order.class, id);
        if (obj == null) {
            return 0;
        }

        Order order = (Order) obj;
        List<OrderItems> items = order.getOrderItemses();
        double total = 0;
        for (OrderItems item : items) {
            total += item.getQuantity() * item.getPrice();
        }
        session.getTransaction().commit();
        return total;
    }

    /**
     * Some thing not worked with these sql, not used temporary.
     * @return
     */
    public List getAllOrder() {
        session.beginTransaction();
//        String sql = "from dbo.Order o " +
//			"inner join dbo.User u on o.customer_id = u.id " +
//			"inner join dbo.Order_Status os on o.order_status_id = os.id " +
//			"left outer join dbo.Order_Items oi on o.id = oi.order_id " +
//			"inner join dbo.Product p on oi.product_id = p.id";
        String sql = "from dbo.Order o "
                + "join fetch o."
                + "inner join dbo.Order_Status os on o.order_status_id = os.id "
                + "left outer join dbo.Order_Items oi on o.id = oi.order_id "
                + "inner join dbo.Product p on oi.product_id = p.id";
        Query query = session.createSQLQuery(sql);
        List result = query.list();
        session.getTransaction().commit();
        return result;
    }
}
